/*
 *	This file is part of Lounge-Cast.
 *
 *  Author:  	Markus Moormann
 *	E-mail:  	mmoorman@uni-osnabrueck.de
 *	Copyright:	University of Osnabrueck (Zentrum Virtuos)
 * 	Website:	www.uni-osnabrueck.de
 *	Date:		16.02.2011
 *
 *	Lounge-Cast is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *	Lounge-Cast is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with Lounge-Cast.  If not, see <http://www.gnu.org/licenses/>.
 */
package backend.service;

import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;

import com.sun.syndication.feed.synd.SyndEntry;
import com.sun.syndication.feed.synd.SyndFeed;
import com.sun.syndication.io.FeedException;
import com.sun.syndication.io.SyndFeedInput;
import com.sun.syndication.io.XmlReader;
import com.twmacinta.util.MD5;

import backend.dbConnection.ConnectionHelper;
import backend.exception.DAOException;
import backend.helper.HTMLUtil;
import backend.helper.HelperClass;
import backend.vo.FeedItemVO;
import backend.vo.FeedVO;
import backend.vo.ImageVO;
import backend.vo.MediaVO;
import backend.vo.PlaylistVO;
import backend.vo.TextVO;
import backend.vo.UserVO;

/**
 * class for feed service
 * 
 * @author Markus Moormann
 */
public class FeedService {
	
	/**
	 * a logger to log errors, debug information, etc.
	 */
	private static Logger LOG = Logger.getLogger(FeedService.class);
	/**
	 * the connection to database
	 */
	private Connection connection;
	/**
	 * the statement so send to database
	 */
	private Statement statement;
	/**
	 * a hashmap mapping the feed_id on a timestamp
	 */
	private static HashMap<Integer, Long> feedUpdates = new HashMap<Integer, Long>();
	 
	/**
	 * gets all feeds from database
	 * 
	 * @return <code>List&lt;FeedVO&gt;</code> containing all Feeds
	 */
	public List<FeedVO> getFeeds() {
		List<FeedVO> result = new LinkedList<FeedVO>();
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String selectFeeds = "SELECT " +
									"f.feed_id," +
									"UNIX_TIMESTAMP(f.feed_last_update) AS feed_last_update," +
									"f.url," +
									"f.title," +
									"f.trust_level," +
									"f.added," +
									"f.added_by," +
									"f.feed_item_valid_time," +
									"f.feed_item_duration," +
									"f.playlist_id," +
									"f.auto_save_to_playlist," +
									"f.add_to_newsticker," +
									"f.background_color," +
									"f.feed_item_background_image," +
									"m.description," +
									"f.feed_item_margin_bottom, " +
									"f.feed_item_margin_left, " +
									"f.feed_item_margin_top, " +
									"f.feed_item_margin_right " +
								"FROM " +
									"feed f " +
								"LEFT JOIN " +
									"media_playlist m " +
									"ON " +
										"(" +
											"f.playlist_id = m.playlist_id" +
										")";
			ResultSet rs = statement.executeQuery(selectFeeds);
			while(rs.next()) {
				FeedVO tmp = new FeedVO();
				tmp.feed_id = rs.getInt("feed_id");
				Date last_update = new Date();
				last_update.setTime(rs.getLong("feed_last_update") * 1000);
				tmp.last_update = last_update;
				tmp.url = rs.getString("url");
				tmp.title = rs.getString("title");
				tmp.trust_level = rs.getInt("trust_level");
				tmp.added = rs.getDate("added");
				tmp.feed_item_valid_time = rs.getLong("feed_item_valid_time");
				tmp.feed_item_duration = rs.getInt("feed_item_duration");
				tmp.auto_save_to_playlist = rs.getBoolean("auto_save_to_playlist");
				tmp.add_to_newsticker = rs.getBoolean("add_to_newsticker");
				tmp.background_color = rs.getInt("background_color");
				tmp.feed_item_background_image = rs.getString("feed_item_background_image");
				tmp.feed_item_margin_bottom = rs.getInt("feed_item_margin_bottom");
				tmp.feed_item_margin_left = rs.getInt("feed_item_margin_left");
				tmp.feed_item_margin_top = rs.getInt("feed_item_margin_top");
				tmp.feed_item_margin_right = rs.getInt("feed_item_margin_right");
				
				PlaylistVO playlist = new PlaylistVO();
				playlist.description = rs.getString("description");
				playlist.playlist_id = rs.getInt("playlist_id");
				tmp.playlist = playlist;
				result.add(tmp);
			}
		} catch (SQLException e) {
			LOG.error("SQLException in getFeeds() - " + e);
			throw new DAOException(e);
		}finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return result;
	}
	
	/**
	 * updates a feed
	 * 
	 * @param feed to feed with all information 
	 * @return boolean on successful update, false else
	 */
	public boolean updateFeed(FeedVO feed) {
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String updateFeed = "UPDATE feed SET " +
									"url 		 = '" + feed.url + "'," +
									"title 		 = '" + feed.title + "'," +
									"trust_level =  " + feed.trust_level + "," +
									"feed_item_valid_time = " + feed.feed_item_valid_time + ", " +
									"playlist_id = " + (feed.playlist.playlist_id == 0 ? 
											PlaylistService.addPlaylist(feed.playlist) : 
											feed.playlist.playlist_id) + "," +
									"background_color = " + feed.background_color + "," +
									"auto_save_to_playlist = '" + feed.auto_save_to_playlist + "'," +
									"add_to_newsticker = '" + feed.add_to_newsticker + "'," +
									"feed_item_duration = " + feed.feed_item_duration + "," +
									"feed_item_background_image = " + 
										(feed.feed_item_background_image == null ? "NULL" : "'" + feed.feed_item_background_image + "'") + "," +
									"feed_item_margin_bottom = " + feed.feed_item_margin_bottom + "," +
									"feed_item_margin_left = " + feed.feed_item_margin_left + "," +
									"feed_item_margin_top = " + feed.feed_item_margin_top + "," +
									"feed_item_margin_right = " + feed.feed_item_margin_right + " " +
								"WHERE " +
									"feed_id = '" + feed.feed_id + "'";
			statement.executeUpdate(updateFeed);
			String update_media_background = 	
					"UPDATE " +
						"text " +
					"SET " +
						"background_image = " + 
						(feed.feed_item_background_image == null ? "NULL" : "'" + feed.feed_item_background_image + "'") + ", " +
						"margin_bottom = " + feed.feed_item_margin_bottom + "," +
						"margin_left = " + feed.feed_item_margin_left + "," +
						"margin_top = " + feed.feed_item_margin_top + "," +
						"margin_right = " + feed.feed_item_margin_right + " " +
					"WHERE " +
						"text.media_id IN" +
						"(" +
							"SELECT " +
								"media_id " +
							"FROM " +
								"media m, " +
								"feed f, " +
								"feed_item fi " +
							"WHERE " +
								"f.feed_id = " + feed.feed_id + " AND " +
								"f.feed_id = fi.feed_id AND " +
								"m.feed_item_uri = fi.feed_item_uri" +
						")";
			LOG.debug(update_media_background);
			statement.execute(update_media_background);
			statement.execute("UPDATE player SET last_update = NOW()");
				
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			return false;
		}finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return true;
	}
	
	/**
	 * delete a feed from database
	 * 
	 * @param feed the feed to delete
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean deleteFeed(FeedVO feed) {
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			String deleteFeed = "DELETE FROM " +
									"feed " +
								"WHERE " +
									"feed_id = " + feed.feed_id;
			String deletePlaylist = "DELETE FROM " +
										"media_playlist " +
									"WHERE " +
										"playlist_id = " + (feed.playlist != null ? feed.playlist.playlist_id : 0);
			
			statement.executeUpdate(deleteFeed);
			statement.executeUpdate(deletePlaylist);
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			return false;
		}finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return true;
	}
	
	/**
	 * update the status of a feed item, a valid status is 'included' or 'excluded'
	 * 
	 * @param feedItem the feedItem to update
	 * @return <code>true</code> on success, <code>false</code> else 
	 */
	public boolean updateFeedItemStatus(FeedItemVO feedItem) {
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String updateFeedItem = "UPDATE feed_item SET " +
										"feed_item_status = '" + feedItem.status + "'" +
									"WHERE " +
										"feed_item_uri = '" + feedItem.uri + "'";
			statement.executeUpdate(updateFeedItem);
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			return false;
		}finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return true;
	}
	
	/**
	 * get feed items from database for frontend
	 * 
	 * @param feed a <code>FeedVO</code> containing the feed_id 
	 * @return a <code>List<List<FeedItemVO>></code> containing a list of included and one list of excluded items
	 */
	public List<List<FeedItemVO>> getFeedItems(FeedVO feed) {
		List<List<FeedItemVO>> result = new LinkedList<List<FeedItemVO>>();
		//updateFeedItems(feed, false);
		
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String selectItems = "SELECT " +
									"feed_item_uri," +
									"feed_id," +
									"feed_item_title," +
									"feed_item_link," +
									"feed_item_description," +
									"feed_item_pubDate," +
									"feed_item_status " +
								"FROM " +
									"feed_item " +
								"WHERE " +
									"feed_id = " + feed.feed_id + " " +
								"ORDER BY " +
									"feed_item_status ASC";
			ResultSet rs = statement.executeQuery(selectItems);
			String lastType = "";
			List<FeedItemVO> typeList = new LinkedList<FeedItemVO>();
			while(rs.next()) {
				if(!lastType.equals(rs.getString("feed_item_status"))) {
					if(typeList.size() > 0) {
						result.add(typeList);
						typeList = new LinkedList<FeedItemVO>();
					}
					lastType = rs.getString("feed_item_status");
				}
				FeedItemVO item = new FeedItemVO();
				item.uri = rs.getString("feed_item_uri");
				item.feed_id = rs.getInt("feed_id");
				item.title = rs.getString("feed_item_title");
				item.link = rs.getString("feed_item_link");
				item.description = rs.getString("feed_item_description");
				item.pubDate = rs.getDate("feed_item_pubDate");
				item.status = rs.getString("feed_item_status");
				typeList.add(item);
			}
			if(typeList.size() > 0)	{
				result.add(typeList);
			}
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		
		return result;
	}
	
	/**
	 * update the feed in database. Items that are not any longer in the feed, are 
	 * deleted. 
	 *  
	 * @param feed the feed to update
	 * @deprecated
	 */
	@SuppressWarnings("unused")
	private void updateFeedItems(FeedVO feed, boolean forceUpdate) {
		try {
			String selectFeedItem = "SELECT " +
										"feed_item_uri " +
									"FROM " +
										"feed_item " +
									"WHERE " +
										"feed_id = " + feed.feed_id + " AND " +
										"feed_item_uri = ? ";
			
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			PreparedStatement ps = connection.prepareStatement(selectFeedItem);
			
			List<String> itemURIs = new LinkedList<String>();
			List<SyndEntry> addableEntries = new LinkedList<SyndEntry>();
			
			Long lastUpdate = feedUpdates.get(feed.feed_id);
			if(lastUpdate == null || (System.currentTimeMillis() - lastUpdate) > 60 * 60 * 1000 || forceUpdate) {
				List<SyndEntry> entries = getEntries(feed.url);
				ResultSet rs;
				for(SyndEntry entry : entries) {
					ps.setString(1, entry.getUri());
					rs = ps.executeQuery();
					if(!rs.next())
					{
						addableEntries.add(entry);
					}
					itemURIs.add("'" + entry.getUri() + "'");
				}
				
				String insertItems = "INSERT INTO " +
										"feed_item " +
											"(" +
												"feed_item_uri," +
												"feed_id," +
												"feed_item_title," +
												"feed_item_link," +
												"feed_item_description," +
												"feed_item_pubDate," +
												"feed_item_status," +
												"feed_item_media_id" +
											")VALUES" +
											"(" +
												"?," +
												"?," +
												"?," +
												"?," +
												"?," +
												"?," +
												"?," +
												"?" +
											")";
				String insertMediaInPlaylist = "INSERT INTO " +
													"media_playlist_items" +
														"(" +
															"playlist_id," +
															"media_id," +
															"position" +
														") VALUES" +
														"(" +
															"?," +
															"?," +
															"?" +
														")";
				String updateFeed = "UPDATE " +
										"feed " +
									"SET " +
										"feed_last_update = NOW() " +
									"WHERE " +
										"feed_id = " + feed.feed_id;
				
				String deleteItems = "DELETE FROM " +
										"feed_item " +
									 "WHERE " +
									 	"feed_item_uri NOT IN (" + 
									 		HelperClass.implode(itemURIs.toArray(new String[itemURIs.size()]), ",") + ") AND " +
									 	"feed_id = " + feed.feed_id;
				
				String deleteMedia = "DELETE FROM " +
										"media " +
									 "WHERE " +
									 	"media_id IN " +
									 	"(" +
									 		"SELECT " +
									 			"feed_item_media_id " +
									 		"FROM " +
									 			"feed_item " +
									 		"WHERE " +
										 		"feed_item_uri NOT IN (" + 
										 			HelperClass.implode(itemURIs.toArray(new String[itemURIs.size()]), ",") + ") AND " +
										 		"feed_id = " + feed.feed_id + 
									 	")";
				//only if there are items to insert
				if(addableEntries.size() > 0) {
					PreparedStatement feedItems = connection.prepareStatement(insertItems);
					PreparedStatement mediaPlaylists = connection.prepareStatement(insertMediaInPlaylist);
					int nr = 0;
					statement.executeUpdate(deleteItems);
					//statement.executeUpdate(deleteMedia);
					for(SyndEntry entry : addableEntries) {
						nr ++;
						Date date = entry.getPublishedDate();
						date = date == null ? entry.getUpdatedDate() : date;
						date = date == null ? new Date() : date;
						
						MD5 media_id = new MD5();
						media_id.Update(entry.getUri());
						
						feedItems.setString(1, entry.getUri());
						feedItems.setInt(2, feed.feed_id);
						feedItems.setString(3, entry.getTitle().trim());
						feedItems.setString(4, entry.getLink());
						feedItems.setString(5, entry.getDescription() == null ? "" : entry.getDescription().getValue());
						feedItems.setString(6, HelperClass.format(date));
						feedItems.setString(7, (feed.add_to_newsticker ? "include" : "exclude"));
						feedItems.setString(8, media_id.asHex());
						feedItems.execute();
						
						//String media_id = null;
						//feed items should be added to playlist automatically
					//	if(feed.auto_save_to_playlist) {
							autoSave(entry, feed);
							mediaPlaylists.setInt(1, feed.playlist.playlist_id);
							mediaPlaylists.setString(2, media_id.asHex());
							mediaPlaylists.setInt(3, nr);
							mediaPlaylists.execute();
					//	}
						
						
					}
				}
				statement.executeUpdate(updateFeed);
				feedUpdates.put(feed.feed_id, System.currentTimeMillis());
			}
			
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
	}
	
	private String autoSave(SyndEntry entry, FeedVO feed) {
		
		MediaVO media = new MediaVO();
		MediaVO imageMedia;
		TextVO text;
		UserVO user = new UserVO();
		user.user_id = 0;
		
		String imageURL = "";
		MD5 media_id = null;
		
		String srcRegex = "(?<=src=['|\"])[^'|\"]*?(?=['|\"])";
		String hrefRegex = "(?<=href=['|\"])[^'|\"]*?(?=['|\"])";
		
		Matcher srcMatch;
		Matcher hrefMatch;
		
		media.reference = feed.url;
		media.media_id = entry.getUri();
		media.title = entry.getTitle().replace("\n", "").trim();
		media.addedBy = user;
		media.duration = feed.feed_item_duration;
		media.approved = feed.trust_level == 1;
		media.valid_date = new Date().getTime()/1000 + feed.feed_item_valid_time;
		media.newsticker = false;
		media.converted = true;
		media.type = "text";
		media.feed_item_uri = entry.getUri();
		
		imageMedia = media.clone();
		imageMedia.media_id += "image";
		imageMedia.type = "image";
		
		//find the image URL if there is one
		srcMatch = Pattern.compile(srcRegex, Pattern.CASE_INSENSITIVE).matcher(entry.getDescription() == null ? "" : entry.getDescription().getValue());
		hrefMatch = Pattern.compile(hrefRegex, Pattern.CASE_INSENSITIVE).matcher(entry.getDescription() == null ? "" : entry.getDescription().getValue());
		
		if(srcMatch.find())
		{
			imageURL = srcMatch.group();
			if(hrefMatch.find())
			{
				imageURL = hrefMatch.group();
			}
		} 
		if(imageURL.equals("")) {
			LOG.debug("no image for this feed item - setting media_id = null");
			imageMedia.media_id = null;
		}
		
		//create the TextVO
		text = new TextVO(media, 
						  HTMLUtil.stripHtmlTags(HTMLUtil.removeHref(HTMLUtil.removeTitle(entry.getDescription() == null ? "" : entry.getDescription().getValue())), "").trim().replaceAll("[\r\n]+", ""),
						  HTMLUtil.stripHtmlTags(HTMLUtil.removeHref(HTMLUtil.removeTitle(entry.getDescription() == null ? "" : entry.getDescription().getValue())), "b,u").trim().replaceAll("[\r\n]+", ""),
						  feed.background_color,
						  "",
						  new ImageVO(imageMedia,
				 			 		  imageURL,
				 			 		  feed.title,
				 			 		  true,
				 			 		  0,
				 			 		  0,
				 			 		  0),
				 		  null,
				 		  "left",
				 		  feed.feed_item_background_image,
				 		  feed.feed_item_margin_bottom,
				 		  feed.feed_item_margin_left,
				 		  feed.feed_item_margin_top,
				 		  feed.feed_item_margin_right);
		LOG.debug(text.background_image);
		//call MediaService to save it
		new MediaService().saveText(text, user);
		
		media_id = new MD5();
		media_id.Update(entry.getUri());
		
		return media_id.asHex();
	}
	
	/**
	 * get all entries from feed
	 * 
	 * @param url the URL of the feed
	 * @return <code>List<SyndEntry></code> containing all FeedItems
	 */
	private List<SyndEntry> getEntries(String url) {
		List<SyndEntry> result = new LinkedList<SyndEntry>();
		URLConnection feedURL;
		try {
			feedURL = new URL(url).openConnection();
			SyndFeedInput input = new SyndFeedInput();
			SyndFeed feed = input.build(new XmlReader(feedURL));
			List<?> tmp = feed.getEntries();
			for(int i = 0; i < tmp.size(); i++) {
				result.add((SyndEntry) tmp.get(i));
			}
		} catch (MalformedURLException e) {
			LOG.error(e);
		} catch (IOException e) {
			LOG.error(e);
		} catch (IllegalArgumentException e) {
			LOG.error(e);
		} catch (FeedException e) {
			LOG.error(e);
		}
		return result;
	}
	
	/**
	 * saves a Feed to database
	 * 
	 * @param feed a <code>FeedVO</code> with information about the feed
	 * @param user a <code>UserVO</code>, the user who added the media
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean saveFeed(FeedVO feed, UserVO user) {
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String feedQuery = 	"INSERT INTO " +
									"`feed` " +
									"(" +
										"`url`," 	 +
										"`added_by`," +
										"`title`," +
										"`playlist_id`," +
										"`add_to_newsticker`," +
										"`feed_item_duration`," +
										"`feed_item_background_image`," +
										"`auto_save_to_playlist`," +
										"`feed_item_margin_bottom`, " +
										"`feed_item_margin_left`, " +
										"`feed_item_margin_top`, " +
										"`feed_item_margin_right` " +
									") VALUES" +
									"(" +
										"'" + feed.url 		+ "'," +
										"'" + user.user_id  + "',"  +
										"'" + feed.title	+ "',"  +
										(feed.playlist.playlist_id == 0 ? 
												PlaylistService.addPlaylist(feed.playlist) :
												feed.playlist.playlist_id) + "," +
										"'" + Boolean.toString(feed.add_to_newsticker) + "'," +
										"" + feed.feed_item_duration + "," +
										(feed.feed_item_background_image == null ? "NULL" : ("'" + feed.feed_item_background_image + "'")) + "," +
										"'true'," +
										feed.feed_item_margin_bottom + "," +
										feed.feed_item_margin_left + "," +
										feed.feed_item_margin_top + "," +
										feed.feed_item_margin_right + "" +
									")";
			statement.execute(feedQuery);
		} catch (SQLException e) {
			LOG.error("Mysql-Error: " + e);
			return false;
		}finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return true;
	}
}
